package com.service.sys;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.common.db.DBDruidUtils;

@Service
public class Synchro {
	static final Logger logger = LoggerFactory.getLogger(Synchro.class);

	DBDruidUtils dbh_meet = new DBDruidUtils("meet", "");
	QueryRunner runner_meet = dbh_meet.getRunner();

	DBDruidUtils dbh = new DBDruidUtils();
	QueryRunner runner = dbh.getRunner();

	public List<Object[]> getOrg() {
		List<Object[]> arrayListResult = null;
		try {
			arrayListResult = runner_meet.query(" select id,subcompanyname,supsubcomid,showorder "
					+ "from cmmanager.oa_hrmsubcompany" + " where canceled=0 ", new ArrayListHandler());

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayListResult;
	}

	public void insertOrg(List<Object[]> list) {
		String sql = "insert into SYS_DEPT(deptid,dwmc,parentid,xssx,disabled,dydwbm,orgtype) values (?,?,?,?,?,?,'company')";
		Object[][] params = new Object[list.size()][6];
		for (int i = 0; i < list.size(); i++) {
			Object[] objects = list.get(i);
			params[i][0] = UUID.randomUUID().toString();
			params[i][1] = objects[1];
			params[i][2] = objects[2];
			params[i][3] = objects[3];
			params[i][4] = "1";
			params[i][5] = objects[0];
		}
		try {
			runner.batch(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<Object[]> getPart_super() {
		List<Object[]> arrayListResult = null;
		try {
			arrayListResult = runner_meet.query(" select id,departmentname,subcompanyid1,showorder "
					+ "from cmmanager.oa_hrmdepartment" + " where canceled=0 AND (SUPDEPID = '0' OR SUPDEPID IS NULL) ",
					new ArrayListHandler());

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayListResult;
	}

	public List<Object[]> getPart() {
		List<Object[]> arrayListResult = null;
		try {
			arrayListResult = runner_meet
					.query(" select id,departmentname,supdepid,showorder " + "from cmmanager.oa_hrmdepartment "
							+ " where canceled=0 AND SUPDEPID!='0'  AND SUPDEPID is not null", new ArrayListHandler());

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayListResult;
	}

	public void insertPart(List<Object[]> list) {
		String sql = "insert into SYS_DEPT(deptid,dwmc,parentid,xssx,disabled,dydwbm,orgtype) values (?,?,?,?,?,?,'department')";
		Object[][] params = new Object[list.size()][6];
		for (int i = 0; i < list.size(); i++) {
			Object[] objects = list.get(i);
			params[i][0] = UUID.randomUUID().toString();
			params[i][1] = objects[1];
			params[i][2] = objects[2];
			params[i][3] = objects[3];
			params[i][4] = "1";
			params[i][5] = objects[0];
		}
		try {
			runner.batch(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<Object[]> getUsers() {
		List<Object[]> arrayListResult = null;
		try {
			arrayListResult = runner_meet.query(
					" select id,lastname,loginid,wx_mobile,email,telephone,departmentid,wx_id,managerid,showorder "
							+ "from cmmanager.oa_hrmresource t "
							+ " where t.status < 4   and t.delete_status = 0 and t.loginid is not null  ",
					new ArrayListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayListResult;
	}

	public void insertUsers(List<Object[]> list) {
		Map<Object, Map<String, Object>> maps = getDept("department");
		String sql = "insert into SYS_USER(userid,deptid,loginname,pwd,disabled,username,usersort,phone,tel,mail,hlevel,casid,wx,superadmin)"
				+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?,'0')";
		Object[][] params = new Object[list.size()][13];
		for (int i = 0; i < list.size(); i++) {
			Object[] objects = list.get(i);
			params[i][0] = UUID.randomUUID().toString();
			if (maps.get(objects[6].toString()) == null) {
				continue;
			}
			params[i][1] = maps.get(objects[6].toString()).get("DEPTID");// deptid
			params[i][2] = objects[2];// loginname
			params[i][3] = "FBFCaK2YwDw=";// pwd
			params[i][4] = "1";//
			params[i][5] = objects[1];// username
			params[i][6] = objects[9];// sort
			params[i][7] = objects[3];// phone
			params[i][8] = objects[5];// tel
			params[i][9] = objects[4];// mail
			params[i][10] = objects[8];// hlevel
			params[i][11] = objects[2];// casid
			params[i][12] = objects[7];// wx
		}
		try {
			runner.batch(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Map<Object, Map<String, Object>> getDept(String type) {
		Map<Object, Map<String, Object>> keyedResult = null;
		try {
			Object[] param = new Object[1];
			param[0] = type;
			keyedResult = runner.query("select DYDWBM,DEPTID from SYS_DEPT where orgtype=?",
					new KeyedHandler<Object>("DYDWBM"), param);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return keyedResult;
	}

	public Map<Object, Map<String, Object>> getDeptAll() {
		Map<Object, Map<String, Object>> keyedResult = null;
		try {
			keyedResult = runner.query("select DYDWBM,DEPTID from SYS_DEPT ", new KeyedHandler<Object>("DYDWBM"));

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return keyedResult;
	}

	public List<Object[]> getDeptList(String type) {
		List<Object[]> arrayListResult = null;
		try {
			Object[] param = new Object[1];
			param[0] = type;
			arrayListResult = runner.query("select DYDWBM,DEPTID from SYS_DEPT where orgtype=?", new ArrayListHandler(),
					param);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayListResult;
	}

	public void updateOrg(String type) {
		List<Object[]> list = getDeptList(type);
		Map<Object, Map<String, Object>> maps = getDept(type);
		try {
			for (int i = 0; i < list.size(); i++) {
				String sql = "update SYS_DEPT set parentid=? where parentid=?";
				Object[] objects = list.get(i);
				String pid = objects[0].toString();
				Object[] params = new Object[2];
				params[0] = maps.get(pid).get("DEPTID");
				params[1] = pid;
				runner.update(sql, params);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updatePartsuper() {
		List<Object[]> list = getDeptList("company");
		Map<Object, Map<String, Object>> maps = getDept("company");
		try {
			for (int i = 0; i < list.size(); i++) {
				String sql = "update SYS_DEPT set parentid=? where parentid=?";
				Object[] objects = list.get(i);
				String pid = objects[0].toString();
				Object[] params = new Object[2];
				params[0] = maps.get(pid).get("DEPTID");
				params[1] = pid;
				runner.update(sql, params);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
